﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using cbmobile.DAC;
using PQT.API;
using cbmobile.BLC;
using System.Data;
using System.Data.SqlClient;
using PQT.API.Connection;

namespace cbmobile.BLC
{
    public class BLC_TinTuc
    {
        SqlConnection cnn = null;
        DBConnection dbCn = new DBConnection("PQT");
        private CBMobileEntities da = null;
        public BLC_TinTuc()
        {
            da = new CBMobileEntities();
        }

        public TinTuc All_News()
        {
            IList < TinTuc > list = da.TinTucs.OrderBy(z => z.ID_News).OrderBy(z => z.TieuDe).ToList();
            if (list.Count == 0)
                return null;
            return list.First();
        }

        public TinTuc Get_News_By_ID(int ID_News)
        {
            IList<TinTuc> list = da.TinTucs.Where(p => p.ID_News == ID_News).ToList();
            if (list.Count == 0)
                return null;
            return list.First();
        }

        

        public DataTable Rows_NewsByListCategory(int TrangThai, string TieuDe)
        {
            DataTable dt = null;
            using (cnn = dbCn.InitConnection())
            {
                cnn.Open();
                SqlDataAdapter da = null;
                DataSet ds = new DataSet();
                SqlCommand cmdUp = new SqlCommand("pr_SearchTinTuc", cnn);
                cmdUp.CommandType = CommandType.StoredProcedure;
                cmdUp.Parameters.Add(new SqlParameter("@TrangThai", SqlDbType.Bit));
                cmdUp.Parameters.Add(new SqlParameter("@TieuDe", SqlDbType.NVarChar));

                cmdUp.Parameters[0].Value = TrangThai;
                cmdUp.Parameters[1].Value = TieuDe;
                try
                {
                    da = new SqlDataAdapter(cmdUp);
                    dt = new DataTable();
                    da.Fill(dt);
                }
                catch { }
                finally
                {
                    if (cnn.State == ConnectionState.Open)
                        cnn.Close();
                    cnn.Dispose();
                }
                
            }
            return dt;
        }

        public DataTable Get_News()
        { 
            DataTable dt = null;
            using (cnn = dbCn.InitConnection())
            {
                cnn.Open();
                SqlDataAdapter da = null;
                DataSet ds = new DataSet();
                SqlCommand cmdUp = new SqlCommand("pr_Get_News", cnn);
                cmdUp.CommandType = CommandType.StoredProcedure;
                try
                {
                    da = new SqlDataAdapter(cmdUp);
                    dt = new DataTable();
                    da.Fill(dt);
                }
                catch { }
                finally
                {
                    if (cnn.State == ConnectionState.Open)
                        cnn.Close();
                    cnn.Dispose();
                }

            }
            return dt;
        }


        public DataTable Get_Top_News()
        {
            DataTable dt = null;
            using (cnn = dbCn.InitConnection())
            {
                cnn.Open();
                SqlDataAdapter da = null;
                DataSet ds = new DataSet();
                SqlCommand cmdUp = new SqlCommand("pr_Get_Top_News", cnn);
                cmdUp.CommandType = CommandType.StoredProcedure;
                try
                {
                    da = new SqlDataAdapter(cmdUp);
                    dt = new DataTable();
                    da.Fill(dt);
                }
                catch { }
                finally
                {
                    if (cnn.State == ConnectionState.Open)
                        cnn.Close();
                    cnn.Dispose();
                }

            }
            return dt;
        }


        public void DeleteNews(int p_ID)
        {
            
            using (cnn = dbCn.InitConnection())
            {
                try
                {
                    cnn.Open();
                    DataSet ds = new DataSet();
                    SqlCommand cmdUp = new SqlCommand("pr_DeleteNews", cnn);
                    cmdUp.CommandType = CommandType.StoredProcedure;
                    cmdUp.Parameters.Add(new SqlParameter("@ID_News", SqlDbType.Int));

                    cmdUp.Parameters[0].Value = p_ID;

                    cmdUp.ExecuteNonQuery();
                }
                catch { }
                finally
                {
                    if (cnn.State == ConnectionState.Open)
                        cnn.Close();
                    cnn.Dispose();
                }
            }

            
        }

        public int GetTotalRecord(string TieuDe)
        {
            return da.TinTucs.Where(z => z.TieuDe.Contains(TieuDe)).Count();
        }


        public bool InsertNews(string TieuDe, int TrangThai, string MoTaNgan, string NoiDung, DateTime NgayTao)
        {
            using (cnn = dbCn.InitConnection())
            {
                cnn.Open();
                SqlCommand cmdIn = new SqlCommand("pr_InsertNews", cnn);
                cmdIn.CommandType = CommandType.StoredProcedure;
                cmdIn.Parameters.Add(new SqlParameter("@TieuDe",SqlDbType.NVarChar));
                cmdIn.Parameters.Add(new SqlParameter("@TrangThai", SqlDbType.Bit));
                cmdIn.Parameters.Add(new SqlParameter("@MoTaNgan", SqlDbType.NVarChar));
                cmdIn.Parameters.Add(new SqlParameter("@NoiDung",SqlDbType.NText));
                cmdIn.Parameters.Add(new SqlParameter("@NgayTao", SqlDbType.Date));

                cmdIn.Parameters[0].Value = TieuDe;
                cmdIn.Parameters[1].Value = TrangThai;
                cmdIn.Parameters[2].Value = MoTaNgan;
                cmdIn.Parameters[3].Value = NoiDung;
                cmdIn.Parameters[4].Value = NgayTao;
                try
                {
                    cmdIn.ExecuteNonQuery();

                    return true;
                }
                catch(Exception e)
                {
                    e.ToString();
                    return false;
                }
            }
            
        }

        


        public bool UpdateNews(int ID_News,string TieuDe, int TrangThai, string MoTaNgan, string NoiDung, DateTime NgayTao)
        {
            using (cnn = dbCn.InitConnection())
            {
                cnn.Open();
                SqlCommand cmdIn = new SqlCommand("pr_UpdateNews", cnn);
                cmdIn.CommandType = CommandType.StoredProcedure;
                cmdIn.Parameters.Add(new SqlParameter("@ID_News", SqlDbType.Int));
                cmdIn.Parameters.Add(new SqlParameter("@TieuDe", SqlDbType.NVarChar));
                cmdIn.Parameters.Add(new SqlParameter("@TrangThai", SqlDbType.Bit));
                cmdIn.Parameters.Add(new SqlParameter("@MoTaNgan", SqlDbType.NVarChar));
                cmdIn.Parameters.Add(new SqlParameter("@NoiDung", SqlDbType.NText));
                cmdIn.Parameters.Add(new SqlParameter("@NgayTao", SqlDbType.Date));

                cmdIn.Parameters[0].Value = ID_News;
                cmdIn.Parameters[1].Value = TieuDe;
                cmdIn.Parameters[2].Value = TrangThai;
                cmdIn.Parameters[3].Value = MoTaNgan;
                cmdIn.Parameters[4].Value = NoiDung;
                cmdIn.Parameters[5].Value = NgayTao;
                try
                {
                    cmdIn.ExecuteNonQuery();

                    return true;
                }
                catch (Exception e)
                {
                    e.ToString();
                    return false;
                }
            }
        }


        public DataTable Views(int ID_News)
        {
            using (cnn = dbCn.InitConnection())
            {
                cnn.Open();
                DataTable dt=null;
                SqlDataAdapter da=null;
                SqlCommand cmdIn = new SqlCommand("pr_SelectNews", cnn);
                cmdIn.CommandType = CommandType.StoredProcedure;
                cmdIn.Parameters.Add(new SqlParameter("@ID_News", cnn));

                cmdIn.Parameters[0].Value = ID_News;
                try
                {
                    da = new SqlDataAdapter(cmdIn);
                    dt = new DataTable();
                    da.Fill(dt);
                }
                catch { }
                finally
                {
                    if (da != null)
                        da.Dispose();
                    if (dt != null)
                        dt.Dispose();
                    cmdIn.Dispose();
                }
                return dt;
            }
        }
    }
}